Model and Analyze Pyramid Usage

Review this guide to learn how to build a data model on Pyramid’s database repository in order to evaluate Pyramid content and analytics usage.

The resulting model will give you insights into:

  • WHO: which users and tenants executed query transactions.
  • WHEN: the time at which query transactions were executed, down to the hour and minute.
  • WHAT: query transactions by data source server and type, database and model, with further details related to content items, the main content folders, and specific hierarchies and measures used in each query (from the “elements” hierarchies).
  • WHERE: query transactions by processing server.
  • WHICH: query transactions by content type.
  • And of course, any combination of the above.

Setup

1. Data Flow

  1. Open a new advanced data flow in Pyramid. Add a relevant data source (PostgreSQL, MS SQL Server, Oracle), depending on which type of repository you have elected to deploy.
    1. From the database listing in the Properties panel, select the Pyramid database repository.
    2. From the Table Selection panel, choose the 'server_log_transaction_columns' table and the 'transactionview'.
    3. Click Add Selected Nodes, to add these tables and views to the data flow.

  2. Go to Column Operations in the Elements panel; connect a Convert Columns node to the 'transactionview' node.
    1. Select the Convert Columns node; from its Properties panel, change the date column type from String to “Date”, with format yyyy-mm-dd.

  3. Go back to the Elements panel and select Targets; connect a new In-Memory target database node and provide a name for the database (“Pyramid Usage”).

2. Data Model:

  1. Change the default measure to “Total Time” on the configuration tab.

  2. On the Tables tab:
    1. Rename the 'server_log_transaction_columns' table to 'Elements'.
    2. Rename the 'transactionview' table to 'Events'.
    3. Make sure the Elements table is inner joined to the Events table using the 'transaction_id' column.

    4. From the Events table, change all measure aggregations from Sum to Average: column_count, row,count, cell_count, connection_time, post_query_time, pre_query_time, other_time, query_time, total_time.

    5. Right click on transaction_id column and add a Count measure.

3. Go to Security and set the required permissions, then save and process your model.

The resulting model will take a snapshot of your transactional activity in Pyramid for further analysis. Setup a schedule on this model to reprocess the database once a day, typically off-peak.